package performance.jmeter;

import performance.BaseTpsTest;

import java.sql.*;
import java.util.Random;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * 多客户端多线程测数据库性能测试 <br/>
 * 不同库同表
 *
 * @author wangwy
 *
 */
public class TpsQueryTest extends BaseTpsTest {

	private static final String ALL_SQL = "select count(1) from score";
	private static final String ID_SQL = "select * from score where id = ";
	private static final String NAME_SQL = "select * from score where name = ";
	private static final String ID_IN_SQL = "select * from score where id in ";
	private static final String ID_BETWEEN_SQL = "select * from score where id between ";
	private static final String USER_AVG_SQL = "select avg(salary) from t_user";
	private static final String USER_ORDER_SQL = "select salary from t_user where id<=? order by salary";
	private static final String UPDATE_ID_SQL = "update score set name='dog' where id = ";
	private static final String UPDATE_NAME_SQL = "update score set name='dog' where name = ";
	private static final String STR_JOIN_SQL = "select a.name,b.name from t_custom a,t_order b where a.id=b.custom_id and a.id=?";

	/**
	 * 测试一定时间内的SQL性能
	 * @throws SQLException
	 */
	public void doQuery(boolean byName) throws Exception {
		//测试长连接(1个线程生命周期只创建一次连接)
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try{
			conn = getConnection();
			String sql;
			if(byName) {
				sql = NAME_SQL;
			}else{
				sql = ID_SQL;
			}
			preparedStatement = conn.prepareStatement(sql + "?");
				if(byName){
					preparedStatement.setString(1, getRecordName());
				}else{
					preparedStatement.setLong(1, getRecordId());
				}

				rs = preparedStatement.executeQuery();
				if(rs.next()){
					rs.getLong("id");
				}else{
					System.out.println("none");
				}
		}finally{
			close(conn);
			close(preparedStatement);
			close(rs);
		}
	}


	/**
	 * 测试一定时间内的SQL性能
	 * @throws SQLException
	 */
	public void doQueryIn() throws Exception {
		//测试长连接(1个线程生命周期只创建一次连接)
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try{
			conn = getConnection();
			String sql = ID_IN_SQL;
			preparedStatement = conn.prepareStatement(sql + "(?,?,?,?)");
			preparedStatement.setLong(1, getRecordId());
			preparedStatement.setLong(2, getRecordId());
			preparedStatement.setLong(3, getRecordId());
			preparedStatement.setLong(4, getRecordId());

			rs = preparedStatement.executeQuery();
			if(rs.next()){
				rs.getInt(1);
			}
		}finally{
			close(conn);
			close(preparedStatement);
			close(rs);
		}
	}

	/**
	 * 测试一定时间内的SQL性能
	 * @throws SQLException
	 */
	public void doQueryAvg(boolean isGroup) throws Exception {
		//测试长连接(1个线程生命周期只创建一次连接)
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try{
			conn = getConnection();
			String sql = USER_AVG_SQL;
			if(isGroup){
				sql += " group by user_dep";
			}
			preparedStatement = conn.prepareStatement(sql);
			rs = preparedStatement.executeQuery();
			while(rs.next()){
				rs.getObject(1);
			}
		}finally{
			close(rs);
			close(preparedStatement);
			close(conn);
		}
	}

	public void doQueryBetween() throws SQLException {
		//测试长连接(1个线程生命周期只创建一次连接)
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try{
			conn = getConnection();
			String sql = ID_BETWEEN_SQL;
			preparedStatement = conn.prepareStatement(sql + "? and ?");
			long start = getRecordId();
			preparedStatement.setLong(1, start);
			preparedStatement.setLong(2, start+3);

			rs = preparedStatement.executeQuery();
			while(rs.next()){
				rs.getLong("id");
			}
		}finally{
			close(rs);
			close(preparedStatement);
			close(conn);
		}
	}


	/**
	 * 测试一定时间内的SQL性能
	 * @throws SQLException
	 */
	public void doQueryOrder(boolean isDesc,int queryNum) throws Exception {
		//测试长连接(1个线程生命周期只创建一次连接)
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try{
			conn = getConnection();
			String sql = USER_ORDER_SQL;
			if(isDesc){
				sql += " desc";
			}
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setInt(1,queryNum);
			rs = preparedStatement.executeQuery();
			for(int i=0;i<10;i++){
				if(rs.next()){
					rs.getObject(1);
				}
			}
		}finally{
			close(rs);
			close(preparedStatement);
			close(conn);
		}
	}

	public void doQueryJoin() throws Exception {
		//测试长连接(1个线程生命周期只创建一次连接)
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try{
			conn = getConnection();
			String sql;
			sql = STR_JOIN_SQL;
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setLong(1, getRecordId());

			rs = preparedStatement.executeQuery();
			while(rs.next()){
				rs.getObject(1);
			}
		}finally{
			close(conn);
			close(preparedStatement);
			close(rs);
		}
	}

	protected long getRecordId(){
		long temp = (long)(Math.random()*100000);
		return temp;
	}

	protected String getRecordName(){
		long temp = (long)(Math.random()*500000);
		String name = "yc"+temp;
		return name;
	}

}
